﻿USE master;
GO
/*建立空的MyPetShop数据库*/
/*请根据实际环境更改数据库文件和日志文件的存放路径*/
CREATE DATABASE OnlineAppointmentSystem
ON 
( NAME = OnlineAppointmentSystem,
    FILENAME = 'C:\Users\Lenovo\Desktop\gitee\OnlineAppointmentSystem.Web\App_Data\OnlineAppointmentSystem.mdf',
    SIZE = 5MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 1MB )
LOG ON
( NAME = OnlineAppointmentSystem_log,
    FILENAME = 'C:\Users\Lenovo\Desktop\gitee\OnlineAppointmentSystem.Web\App_Data\OnlineAppointmentSystem_log.ldf',
    SIZE = 3MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 1MB )
COLLATE Chinese_PRC_CS_AS;
GO
USE OnlineAppointmentSystem
/*分别建立user、department、doctor、disease、timeSlots、doctorAvailavility、edicalCconsultation等数据表的结构*/

CREATE TABLE [dbo].[user]
(
	[Id] INT NOT NULL PRIMARY KEY, 
    [name] VARCHAR(50) NOT NULL, 
    [sex] VARCHAR(10) NULL, 
    [email] VARCHAR(50) NULL, 
    [phone] VARCHAR(50) NOT NULL, 
    [address] VARCHAR(50) NULL, 
    [pwd] VARCHAR(50) NULL, 
    [status] INT NOT NULL DEFAULT 0
)

CREATE TABLE [dbo].[department]
(
	[name] VARCHAR(50) NOT NULL PRIMARY KEY, 
    [description] VARCHAR(50) NULL
)

CREATE TABLE [dbo].[doctor]
(
	[Id] INT NOT NULL PRIMARY KEY, 
    [name] VARCHAR(50) NOT NULL, 
    [depName] VARCHAR(50) NOT NULL, 
    [sex] VARCHAR(10) NOT NULL, 
    [age] INT NOT NULL
)

CREATE TABLE [dbo].[disease]
(
	[name] VARCHAR(50) NOT NULL PRIMARY KEY, 
    [description] VARCHAR(50) NULL, 
    [depName] NCHAR(10) NOT NULL
)

CREATE TABLE [dbo].[timeSlots]
(
	[id] INT NOT NULL PRIMARY KEY, 
    [startTime] TIME NOT NULL, 
    [endTime] NCHAR(10) NOT NULL, 
    [dayOfWeek] VARCHAR(20) NOT NULL, 
    [maxCapacity] INT NOT NULL
)

CREATE TABLE [dbo].[doctorAvailavility]
(
	 
    [slotid] INT NOT NULL,
    [doctorid] INT NOT NULL ,
	PRIMARY KEY ([slotid],[doctorid])
)


CREATE TABLE [dbo].[MedicalCconsultation]
(
	[Id] INT NOT NULL PRIMARY KEY, 
    [score] INT NULL, 
    [comment] VARCHAR(50) NULL, 
    [userId] INT NOT NULL, 
    [slotid] INT NULL, 
    [appointmentTime] INT NOT NULL, 
    [doctorid] INT NOT NULL
)



